import wx
import sys

try:
    import sqlite3 as sqlite
except:
    from pysqlite2 import dbapi2 as sqlite

import os
import re


class DbHelper:
    def __init__(self, sl3db, new=False):
        sl3dir = os.path.dirname(sl3db)
        
        if not new:
            self.con = sqlite.connect(sl3db)
            self.cur = self.con.cursor()
        else:
            self.con = sqlite.connect(sl3db)
            self.cur = self.con.cursor()
            self.cur.executescript(SqlScript)
    def Disconnect(self):
        self.con.close()
    def SaveNote(self,note):
        if (note.get("NoteID") == -1) or (note.get("NoteID") == None):
            self.cur.execute("insert into notes values(null, '%s', '%s', datetime('now','localtime'))" % 
                        (note.get('tag'), note.get('content')))
            newNoteID = self.cur.execute("select last_insert_rowid()").fetchone()[0]
            note.update( {'NoteID': newNoteID} )
        else:
            noteid = note.get("NoteID")
            self.cur.execute("update notes set note_tag = '%s', note_content = '%s', pg_date = datetime('now','localtime') where note_id=%d" % 
                        (note.get('tag'), note.get('content'), int(noteid)))
             
        self.con.commit()
    def GetNotes(self,filtertext = None):
        
        if filtertext:
            sql = "select note_id, note_tag, note_content from notes where note_tag like '%s' order by note_id "%("%" + filtertext + "%")
            self.cur.execute(sql)
        else:       
            self.cur.execute('select note_id, note_tag, note_content from notes order by note_id')
        
        
        fetch = self.cur.fetchall()
        
        notes = []
        for note_id,note_tag, note_content in fetch:
            notes.append( {'tag':note_tag,'content': note_content, 'ID':note_id} )       
        return notes
    def GetNotesByID(self,noteID = None):
        notes = []
        if None == noteID: return
    
        sql = "select note_id, note_tag, note_content from notes where note_id = '%d' "%(noteID)
        self.cur.execute(sql)
        fetch = self.cur.fetchall()
        
        for note_id,note_tag, note_content in fetch:
            notes.append( {'tag':note_tag,'content': note_content, 'ID':note_id} )       
        return notes
    
        
